var express = require('express')
var router = express.Router()

const { getALL, getById, exec } = require('../db')

//获取所有文章:GET/articles
router.get('/', async (req, res) => {
  //解析请求参数
  // console.log(req.body)   {}
  const { page = 1, size = 5 } = req.query
  let sql = `select count(*) as total from articles`
  let { total } = await getById(sql)
  //定义偏移量
  let offset = (page - 1) * size
  //操作数据库
  sql = `select articles.id,title,create_time,abstract,category_name from articles LEFT OUTER JOIN categories ON categories.id = articles.category_id order by articles.id desc limit ${offset},${size}  `
  // console.log(sql)
  const data = await getALL(sql)
  res.send({
    code: 0,
    message: '获取所有文章成功',
    result: {
      total,
      data,
    },
  })
})

// 模糊查询
router.get('/filter', async (req, res) => {
  // 解析请求参数
  const { page = 1, size = 5, filterContent } = req.query
  // 编写sql语句
  let sql = `select count(*) as total from articles where abstract like '%${filterContent}%' or title like '%${filterContent}%'`
  let { total } = await getById(sql)
  //定义偏移量
  let offset = (page - 1) * size
  sql = `select articles.id,title,create_time,abstract,category_name from articles LEFT OUTER JOIN categories ON categories.id = articles.category_id where abstract like '%${filterContent}%' or title like '%${filterContent}%' limit ${offset},${size}`
  console.log(sql)
  // 返回结果
  const data = await getALL(sql)
  res.send({
    code: 0,
    message: '获取查询文章成功',
    result: {
      total,
      data,
    },
  })
})

//添加文章:POST/articles
router.post('/', async (req, res) => {
  //解析参数
  const { title, author, abstract, content, imageUrl, category } = req.body
  //操作数据库
  // INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
  let sql = `insert into articles (title, author, abstract, content, image_address, category_id) values('${title}', '${author}', '${abstract}', '${content}', '${imageUrl}', '${category}')`
  console.log(sql)
  const data = await exec(sql)
  res.send({
    code: 0,
    message: '添加文章成功',
    result: {
      id: data.insertId,
    },
  })
})

// 获取相关信息文章:

/**
 * 根据id获取单篇文章数据
 *GET/articles/id
 */
router.get('/:id', async (req, res) => {
  const { id } = req.params
  let sql = `select * from articles where id=${id}`
  const data = await getById(sql)
  res.send({
    code: 0,
    message: '获取单篇文章成功',
    result: data,
  })
})

//put:更新文章
// put/articles/id
router.put('/:id', async (req, res) => {
  const { id } = req.params
  const { title, author, abstract, content, imageUrl, category } = req.body
  let sql = `update articles set title='${title}', author='${author}',abstract='${abstract}',content='${content}',image_address='${imageUrl}',category_id='${category}' where id = ${id}`
  console.log(sql)
  const data = await exec(sql)
  res.send({
    code: 0,
    message: '更新文章成功',
    result: {
      data,
    },
  })
})

//删除文章:DELETE/articles/id
router.delete('/:id', async (req, res) => {
  const { id } = req.params

  // 查询删除i对应的图像url
  let sql = `select image_address from articles where id =${id}`
  const data = await getById(sql)
  const { image_address } = data

  sql = `delete from articles where id = ${id}`
  console.log(sql)

  await exec(sql)
  res.send({
    code: 0,
    message: '删除文章成功',
    result: image_address,
  })
})
module.exports = router
